package preprocessor;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class GetDataFromSQL {

	
	private static BufferedWriter fileOutput;
	
	
	public static void main(String [] args) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, IOException{

		
		fileOutput = new BufferedWriter( new FileWriter("output.txt"));
		
		
		//
		// Class ofro intereacting the with SQLITE
		//
		Class.forName("org.sqlite.JDBC");
		Connection sqliteCon  = DriverManager.getConnection("jdbc:sqlite:test.db");


		//
		// Class for intereacting the with mySQL
		//
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		
        //mConnect = DriverManager.getConnection("jdbc:mysql:wordnet30?user=root&password=3mc8x0n1");  
		//Connection mysqlCon = DriverManager.getConnection("jdbc:mysql:wordnet30?user=root&password=3mc8x0n1");
		
		Connection mysqlCon = DriverManager.getConnection("jdbc:mysql://127.0.0.1","root", "3mc8x0n1");



		//
		// Creates staments for the two connections
		//
		Statement sqliteStat = sqliteCon.createStatement();
		Statement mysqlStat = mysqlCon.createStatement();

		
		mysqlStat.executeUpdate("use wordnet30;");

		
		StringBuilder myBuilder = new StringBuilder();
		
		myBuilder.append("select ");
		
		
		myBuilder.append("(select words.lemma from words, wordsXsensesXsynsets ");
		myBuilder.append("where words.wordid = wordsXsensesXsynsets.wordid AND semlinks.synset1id = wordsXsensesXsynsets.synsetid   LIMIT 1) as wordOne, ");


		myBuilder.append("(select words.lemma from words, wordsXsensesXsynsets ");
		myBuilder.append("where words.wordid = wordsXsensesXsynsets.wordid AND semlinks.synset2id = wordsXsensesXsynsets.synsetid   LIMIT 1) as wordTwo "); 
							

		myBuilder.append("from semlinks");


		//System.out.println(myBuilder.toString());
		
		ResultSet linkData = mysqlStat.executeQuery(myBuilder.toString());
		
		
		int tempId1 = 0;
		int tempId2 = 0;
				
		while (linkData.next()){

			//
			// Asks weather the first node has been created, if it hasn't been create it
			//			
			tempId1  = createNode(sqliteStat, linkData.getString("wordOne"));
			
			//
			// Asks weather the second has been created, if it hasn't been create it.
			//
			tempId2 = createNode(sqliteStat, linkData.getString("wordTwo"));
			
			
			
			//
			// INserts the new link into the SQLite database
			//
			sqliteStat.executeUpdate("insert into Links (Node1, Node2, lWeight) values (" + tempId1 + ", " + tempId2 +  ", 1)");
			
		}

		linkData.close();
		sqliteCon.close();
		mysqlCon.close();    
		fileOutput.close();
		
	}
	
	
	/*
	 * Asks if the node has already been created if so it returns
	 * 
	 */
	public static int createNode(Statement sqliteStat, String nodeName) throws SQLException{
		
		String tempNodeName = nodeName.replaceAll("'", "");
		
		String query = "SELECT * from Nodes where Name = '" + tempNodeName  + "';";
		
		//fileOutput.write(" \n");
		//System.out.println(query);
		
		
		ResultSet linkData = sqliteStat.executeQuery(query);
		int tempId = 0;
		
		if(linkData.next()){
			
			tempId = linkData.getInt("nKey");
			
		} else {
			
			sqliteStat.executeUpdate("insert into Nodes (Name,Type) values ('" + tempNodeName +  "', 'WORD');");
			
			ResultSet linkData2 = sqliteStat.executeQuery("SELECT * from Nodes where Name = '" + tempNodeName  + "';");
			linkData.next();
			
			tempId = linkData.getInt("nKey");
			
			linkData2.close();
		}
		
		
		linkData.close();
		
		return tempId;
		
		
	}
	
}

